---
title: "pk82036_task stored procedure documentation"
summary: "Analysis of Oracle package pk82036_task (WCIS_DBA), the task CRUD/purge package backing SAIF.Business.Tasks."
status: draft
owner: SAIF Documentation Generator
last_reviewed: 2026-08-31
source_schema: "WCIS_DBA"
source_name: "pk82036_task"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name pk82036_task"
retrieval_date: "2026-08-31"
---

# pk82036_task

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package |
| **Database** | WCIS_DBA / Oracle |
| **Calling libraries** | `SAIF.Business.Tasks` |
| **Calling classes** | `clsTask`, `clsTaskCollection` |

## Purpose

`pk82036_task` is the Oracle PL/SQL package behind the SAIF task/reminder subsystem. It implements CRUD for a single `TASK` row (`task_insert`, `task_select`, `task_update`, `task_delete`) and a scheduled/batch purge of previously soft-deleted rows (`Task_Purge`). Every create/update/delete operation is routed through this package by `clsTask`'s framework lifecycle hooks (`Before*`/`After*`), and the purge operation is exposed as a public shared API surface on `clsTaskCollection` consumed by three downstream projects.

## Live-caller confirmation

`SAIF.Business.Tasks/clsTask.vb` and `SAIF.Business.Tasks/clsTaskCollection.vb` are live, reachable callers of these package members — they are **not dead code**:

- `clsTask.vb` defines the four SQL statement names on its `CriteriaBase` class (`clsTask.vb:1276–1295`) as implementations of the `IBusinessCriteria.DeleteSQL` / `InsertSQL` / `RetrieveSQL` / `UpdateSQL` contract. Each is consumed by a `Protected Overrides` SAIF BOF data-portal lifecycle hook in the same file, which assigns it to `DataRequest.SQLStatement` with `CommandType = CommandType.StoredProcedure` and then binds parameters:
  - `BeforeDelete` → `MyCriteria.DeleteSQL` (`clsTask.vb:696`), paired with `AfterDelete`, which reads the return value.
  - `BeforeInsert` → `MyCriteria.InsertSQL` (`clsTask.vb:725`), paired with `AfterInsert`.
  - `BeforeRetrieve` → `MyCriteria.RetrieveSQL` (`clsTask.vb:772`), paired with `AfterRetrieve`.
  - `BeforeUpdate` → `MyCriteria.UpdateSQL` (`clsTask.vb:799`).
  Because these are framework `Overrides` invoked by the BOF data portal, they execute on every persist/fetch of a `clsTask` instance and cannot be dead code.
- `clsTaskCollection.PurgeTasks` (`clsTaskCollection.vb:246`) is a `Public Shared Function` that assigns `"PK82036_Task.Task_Purge"` to `DataRequest.SQLStatement` (`clsTaskCollection.vb:253`) and executes it via `inDataPortalInfo.ProcessPortalRequest`. It is a public API surface member of a shipped assembly consumed by downstream projects: `SAIF.Application.Claim.Legal.vbproj:205`, `SAIF.Application.Claim.vbproj:188`, and `SAIF.Business.Tasks.Claim.vbproj:183` all declare a `ProjectReference` to `SAIF.Business.Tasks`.

## Overload note

The retrieved package declares `task_insert`, `task_update`, and `task_delete` **twice each** — once as a `FUNCTION … RETURN NUMBER` and once as a legacy `PROCEDURE` overload. The package spec marks the procedure overloads with the comment `-- REMOVE THESE AFTER ALL APPS are using the version of saifTasks created in August 2007.`, and the body carries `-- delete the procedures after all apps are using the functions`. The .NET callers bind `DataRequest.AddParm("RowsAffected", m_RowsAffected, ParameterDirection.ReturnValue)` on insert/update/delete, which resolves to the **FUNCTION** overloads*. The sections below document the function overloads as the live signatures; the deprecated procedure overloads are retained here only as historical context and are not otherwise documented.

## Procedures / Methods

### task_insert

| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` (`SQL%ROWCOUNT`) — packaged **function** (legacy procedure overload also present, deprecated) |
| **Called by** | `clsTask.BeforeInsert` / `clsTask.AfterInsert` via `CriteriaBase.InsertSQL` |

**Description:** Inserts a new row into `TASK` with a sequence-assigned key, server-side audit stamps, an active (`'N'`) delete flag, and zero-to-null normalisation of claim and policy references. Returns the affected row count.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `taskdate` | IN | `task.task_date%TYPE` | — | Task due date; bound from `TaskDate`. |
| `tasktext` | IN | `task.task_text%TYPE` | — | Task free text (size 4000); bound from `TaskText`. |
| `taskuser` | IN | `task.task_user%TYPE` | — | Assigned user (size 30); bound from `MailID`. |
| `taskprioritycode` | IN | `task.task_priority_code%TYPE` | — | Priority code (size 5); bound from `TaskPriorityCode`. |
| `taskcategorycode` | IN | `task.task_category_code%TYPE` | — | Category code (size 30); bound from `TaskCategoryCode`. |
| `tasksourcecode` | IN | `task.task_source_code%TYPE` | — | Source system code (size 5); bound from `TaskSourceCode`. |
| `taskactioncode` | IN | `task.task_action_code%TYPE` | — | Action code (size 5); bound from `TaskActionCode`. |
| `notepadflag` | IN | `task.notepad_flag%TYPE` | — | Notepad-entry flag (size 1); bound from `NotepadFlag`. |
| `deleteflag` | IN | `task.delete_flag%TYPE` | — | Delete flag (size 1); bound from `DeleteFlag` but **not used** — the body hard-codes `'N'` instead. |
| `claimid` | IN | `task.claim_id%TYPE` | — | Claim identifier; bound from `ClaimID`. `0` is normalised to `NULL`. |
| `policynum` | IN | `task.policy_num%TYPE` | — | Policy number; bound from `PolicyNum`. `0` is normalised to `NULL`. |
| `imageseqnum` | IN | `task.image_seq_num%TYPE` | — | Image sequence number; bound from `ImageSeqNum`. |
| `contextcode` | IN | `task.context_code%TYPE` | — | Context category code (size 5); bound from `ContextCode`. |
| `contextkey` | IN | `task.context_key%TYPE` | — | Context key (size 100); bound from `ContextKey`. |
| `contextcategory` | IN | `task.context_category%TYPE` | — | Context category (size 100); bound from `ContextCategory`. |
| `contexttext` | IN | `task.context_text%TYPE` | — | Context text (size 4000); bound from `ContextText`. |
| `contextdate` | IN | `task.context_date%TYPE` | — | Context date; bound from `ContextDate`. |
| `contextnum` | IN | `task.context_num%TYPE` | — | Context numeric value; bound from `ContextNum`. |
| `createuser` | IN | `task.create_user%TYPE` | — | Creating user (size 30); bound from `CreateUser`. |
| `updateuser` | IN | `task.update_user%TYPE` | — | Updating user (size 30); bound from `UpdateUser`. |
| `tasksecurity` | IN | `task.task_security%TYPE` | — | Task security value (size 30); bound from `TaskSecurity`. |
| *(return)* | RETURN | `NUMBER` (`SQL%ROWCOUNT`) | — | Rows affected; bound to `RowsAffected` via `ParameterDirection.ReturnValue`. |

### task_select

| Field | Value |
| --- | --- |
| **Returns** | `recordset` (`REF CURSOR`) — packaged procedure |
| **Called by** | `clsTask.BeforeRetrieve` / `clsTask.AfterRetrieve` via `CriteriaBase.RetrieveSQL` |

**Description:** Opens a REF CURSOR over a single `TASK` row selected by `task_seq_num`, projecting 25 columns under camel-cased aliases consumed by `clsTask`'s fill logic. No predicate other than the key restricts the fetch — soft-deleted rows (`delete_flag = 'Y'`) are returned like any other row.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `taskseqnum` | IN | `task.task_seq_num%TYPE` | — | Task surrogate key; bound from `TaskID` (itself bound from `TaskSeqNum`). |
| `recordset` | IN OUT | `ref_cursor` (`REF CURSOR`) | — | Matching task row, via `DataRequest.AddCursorParm("recordset")`. |

### task_update

| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` (`SQL%ROWCOUNT`) — packaged **function** (legacy procedure overload also present, deprecated) |
| **Called by** | `clsTask.BeforeUpdate` via `CriteriaBase.UpdateSQL` |

**Description:** Updates the mutable attributes of an existing `TASK` row under an optimistic-concurrency check (`task_seq_num = taskseqnum AND update_seq_num = updateseqnum`), advancing `update_seq_num` and re-stamping `update_date` / `update_user`. Task source code and creator are immutable and are not accepted as parameters.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `taskseqnum` | IN | `task.task_seq_num%TYPE` | — | Task surrogate key; bound from `TaskSeqNum`. |
| `taskdate` | IN | `task.task_date%TYPE` | — | Task due date; bound from `TaskDate`. |
| `tasktext` | IN | `task.task_text%TYPE` | — | Task free text (size 4000); bound from `TaskText`. |
| `taskuser` | IN | `task.task_user%TYPE` | — | Assigned user (size 30); bound from `TaskUser`. |
| `taskprioritycode` | IN | `task.task_priority_code%TYPE` | — | Priority code (size 5); bound from `TaskPriorityCode`. |
| `taskcategorycode` | IN | `task.task_category_code%TYPE` | — | Category code (size 30); bound from `TaskCategoryCode`. |
| `taskactioncode` | IN | `task.task_action_code%TYPE` | — | Action code (size 5); bound from `TaskActionCode`. |
| `notepadflag` | IN | `task.notepad_flag%TYPE` | — | Notepad-entry flag (size 1); bound from `NotepadFlag`. |
| `deleteflag` | IN | `task.delete_flag%TYPE` | — | Delete flag (size 1); bound from `DeleteFlag`. |
| `claimid` | IN | `task.claim_id%TYPE` | — | Claim identifier; bound from `ClaimID`. `0` is normalised to `NULL`. |
| `policynum` | IN | `task.policy_num%TYPE` | — | Policy number; bound from `PolicyNum`. `0` is normalised to `NULL`. |
| `imageseqnum` | IN | `task.image_seq_num%TYPE` | — | Image sequence number; bound from `ImageSeqNum`. |
| `contextcode` | IN | `task.context_code%TYPE` | — | Context category code (size 5); bound from `ContextCode`. |
| `contextkey` | IN | `task.context_key%TYPE` | — | Context key (size 100); bound from `ContextKey`. |
| `contextcategory` | IN | `task.context_category%TYPE` | — | Context category (size 100); bound from `ContextCategory`. |
| `contexttext` | IN | `task.context_text%TYPE` | — | Context text (size 4000); bound from `ContextText`. |
| `contextdate` | IN | `task.context_date%TYPE` | — | Context date; bound from `ContextDate`. |
| `contextnum` | IN | `task.context_num%TYPE` | — | Context numeric value; bound from `ContextNum`. |
| `updateuser` | IN | `task.update_user%TYPE` | — | Updating user (size 30); bound from `UpdateUser`. |
| `updateseqnum` | IN | `task.update_seq_num%TYPE` | — | Optimistic-concurrency version token; bound from `UpdateSeqNum`. |
| `tasksecurity` | IN | `task.task_security%TYPE` | — | Task security value (size 30); bound from `TaskSecurity`. |
| *(return)* | RETURN | `NUMBER` (`SQL%ROWCOUNT`) | — | Rows affected; bound to `RowsAffected` via `ParameterDirection.ReturnValue`. |

> Note: `task_update` has **no** `tasksourcecode` and no `createuser` parameter — source code and creator are immutable after insert.

### task_delete

| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` (`SQL%ROWCOUNT`) — packaged **function** (legacy procedure overload also present, deprecated) |
| **Called by** | `clsTask.BeforeDelete` / `clsTask.AfterDelete` via `CriteriaBase.DeleteSQL` |

**Description:** **Soft-deletes** a `TASK` row by setting `delete_flag = 'Y'` under an optimistic-concurrency check, rather than physically removing it, preserving the row for history and later purge by `Task_Purge`.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `taskseqnum` | IN | `task.task_seq_num%TYPE` | — | Task surrogate key; bound from `TaskSeqNum`. |
| `updateseqnum` | IN | `task.update_seq_num%TYPE` | — | Optimistic-concurrency version token; bound from `UpdateSeqNum`. |
| `updateuser` | IN | `task.update_user%TYPE` | — | Updating user (size 30); bound from `UpdateUser`. |
| *(return)* | RETURN | `NUMBER` (`SQL%ROWCOUNT`) | — | Rows affected; bound to `RowsAffected` via `ParameterDirection.ReturnValue`. |

### task_purge

| Field | Value |
| --- | --- |
| **Returns** | N/A (procedure); `outdelrows OUT VARCHAR2` carries the deleted row count |
| **Called by** | `clsTaskCollection.PurgeTasks` (`Public Shared`) |

**Description:** Physically removes previously soft-deleted `TASK` rows (`delete_flag = 'Y'`) for a given source and category combination whose last update is older than a caller-supplied retention window, in whole days.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `intasksourcecode` | IN | `task.task_source_code%TYPE` | — | Source-system code to scope the purge (size 5); bound from `intasksourcecode`. |
| `intaskcategorycode` | IN | `task.task_category_code%TYPE` | — | Category code to scope the purge (size 5); bound from `intaskcategorycode`. |
| `inkeepdays` | IN | `VARCHAR2` | — | Retention window in days, passed as a string (size 5); bound from `inkeepdays`. |
| `outdelrows` | OUT | `VARCHAR2` | — | Deleted row count as text (size 15); read back and `CInt`-converted to the calling function's `Integer` return. |

## Business Rules

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-BT-001 | A zero claim identifier is not a real claim and must not be stored, because it breaks referential integrity on `TASK`. | `claimid = 0` | Store `NULL` in `task.claim_id` via `decode(claimid, 0, null, claimid)`. Documented inline: *"Modified to force 0 claimid and policynum to null. A 0(zero) claimid or policynum does not make sense and is preventing RI on the table."* |
| PR-BT-002 | A zero policy number is not a real policy and must not be stored. | `policynum = 0` | Store `NULL` in `task.policy_num` via `decode(policynum, 0, null, policynum)`. |
| PR-BT-003 | A newly created task is always active; the caller cannot insert a task in a deleted state. | Always, on insert | `delete_flag` is hard-coded to `'N'` in the `VALUES` list, ignoring the supplied `deleteflag` argument. |
| PR-BT-004 | Task identity and audit stamps are assigned by the database, not the caller. | Always, on insert | `task_seq_num := task_seq.NEXTVAL`; `create_date := SYSDATE`; `update_date := SYSDATE`; `update_seq_num := 0`. |
| PR-BT-005 | The caller must be able to detect a failed insert. | Always, on insert | Returns `SQL%ROWCOUNT`. `clsTask.AfterInsert` raises the error `"Task Insert Failed"` when `RowsAffected <> 1`, otherwise calls `MarkOld()`. |
| PR-BT-006 | A task is retrieved by its surrogate key only; no other predicate (including `delete_flag`) restricts the fetch. | `task.task_seq_num = taskseqnum` | Opens `recordset` over a 25-column projection of `TASK` with camel-cased column aliases matching the `clsTask` property names. Soft-deleted rows (`delete_flag = 'Y'`) **are** returned. |
| PR-BT-007 | An update must not overwrite a concurrent modification made by another user (optimistic concurrency). | `task_seq_num = taskseqnum AND update_seq_num = updateseqnum` | If the version token does not match, zero rows update and `SQL%ROWCOUNT = 0` is returned to the caller. |
| PR-BT-008 | Every successful update advances the row version and re-stamps the audit trail. | On matching row | `update_seq_num := updateseqnum + 1`; `update_date := SYSDATE`; `update_user := updateuser`. |
| PR-BT-009 | The zero-to-null normalisation for claim and policy applies on update as well as insert. | `claimid = 0` or `policynum = 0` | `claim_id := decode(claimid, 0, null, claimid)`; `policy_num := decode(policynum, 0, null, policynum)`. |
| PR-BT-010 | Task source and creator are immutable. | Always | Neither `task_source_code` nor `create_user` / `create_date` appears in the `SET` list, and no `tasksourcecode` / `createuser` parameter is exposed. |
| PR-BT-011 | Task deletion is logical, not physical — task history is retained. | Always, on delete | Issues `UPDATE task SET delete_flag = 'Y'` rather than `DELETE`. |
| PR-BT-012 | A delete must not clobber a concurrent modification (same optimistic-concurrency contract as update). | `task_seq_num = taskseqnum AND update_seq_num = updateseqnum` | Zero rows affected when stale; `clsTask.AfterDelete` raises `clsConstants.Messages.DBContentionError` under the `"DeleteError"` key when `RowsAffected <> 1`. |
| PR-BT-013 | A logical delete is itself an auditable change and advances the row version. | On matching row | `update_date := SYSDATE`; `update_seq_num := updateseqnum + 1`; `update_user := updateuser`. |
| PR-BT-014 | Only tasks already logically deleted are eligible for physical removal. | `delete_flag = 'Y'` | Rows not soft-deleted are never physically purged. |
| PR-BT-015 | Purging is scoped to a single source/category combination — a caller cannot purge across the whole `TASK` table in one call. | `task_source_code = intasksourcecode AND task_category_code = intaskcategorycode` | Both predicates are mandatory `AND` conditions on the `DELETE`. |
| PR-BT-016 | A retention window is enforced by whole days, not by timestamp, so a task is retained for at least `inkeepdays` full days after its last update. | `TRUNC(update_date) < TRUNC(SYSDATE - TO_NUMBER(inkeepdays))` | Both sides of the comparison are `TRUNC`-ed to date granularity before comparison. |
| PR-BT-017 | The caller must be told how many rows were physically removed. | Always | `outdelrows := TO_CHAR(SQL%ROWCOUNT)`; `clsTaskCollection.PurgeTasks` reads it back with `CInt(DataRequest.ParmValue("outdelrows"))` and returns it as `Integer`. |

> These rules are documented in [business-rules.md](../business-rules.md) under [Stored procedure business rules](../business-rules.md#stored-procedure-business-rules).

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| Create task | Inserts a new row into `TASK` with a sequence-assigned key, server-side audit stamps, an active (`'N'`) delete flag, and zero-to-null normalisation of claim and policy references. Returns the affected row count. | `clsTask` insert through the SAIF BOF data portal — `clsTask.BeforeInsert` binds parameters, `clsTask.AfterInsert` validates `RowsAffected = 1`. |
| Retrieve task | Opens a REF CURSOR over a single `TASK` row selected by `task_seq_num`, projecting 25 columns under camel-cased aliases consumed by `clsTask.Fill`. | `clsTask` retrieve through the data portal — `clsTask.BeforeRetrieve` binds `TaskID` and a cursor parameter, `clsTask.AfterRetrieve` reads the `clsSAIFDataReader` and calls `MarkOld()`. |
| Update task | Updates the mutable attributes of an existing `TASK` row under an optimistic-concurrency check, advancing `update_seq_num` and re-stamping `update_date` / `update_user`. Source code and creator are not updatable. | `clsTask` update through the data portal — `clsTask.BeforeUpdate` binds parameters plus the key/version pair. |
| Logically delete task | Marks a `TASK` row as deleted by setting `delete_flag = 'Y'` under an optimistic-concurrency check, preserving the row for history and later purge. | `clsTask` delete through the data portal — `clsTask.BeforeDelete` binds key/version/user, `clsTask.AfterDelete` raises a DB-contention error if the row count is not 1. |
| Purge aged deleted tasks | Physically removes previously soft-deleted `TASK` rows for a given source and category whose last update is older than a caller-supplied retention window in days, and returns the deleted row count. | Explicit call to the `Public Shared` `clsTaskCollection.PurgeTasks(inDataPortalInfo, inTaskSourceCode, inTaskCategoryCode, inKeepDays)`; the data request is tagged `"IMG0001_Purge_Task"` and executed as `DataRequestType.Scalar`. |

> These operations are also listed in [operations-processes.md](../operations-processes.md) under [Stored procedure operations](../operations-processes.md#stored-procedure-operations), with source reference back to this document.

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
PACKAGE pk82036_task
AS
   TYPE ref_cursor IS REF CURSOR;

   PROCEDURE task_select(
      taskseqnum   IN       task.task_seq_num%TYPE
    , recordset    IN OUT   ref_cursor);

   FUNCTION task_insert(
      taskdate           IN   task.task_date%TYPE
    , tasktext           IN   task.task_text%TYPE
    , taskuser           IN   task.task_user%TYPE
    , taskprioritycode   IN   task.task_priority_code%TYPE
    , taskcategorycode   IN   task.task_category_code%TYPE
    , tasksourcecode     IN   task.task_source_code%TYPE
    , taskactioncode     IN   task.task_action_code%TYPE
    , notepadflag        IN   task.notepad_flag%TYPE
    , deleteflag         IN   task.delete_flag%TYPE
    , claimid            IN   task.claim_id%TYPE
    , policynum          IN   task.policy_num%TYPE
    , imageseqnum        IN   task.image_seq_num%TYPE
    , contextcode        IN   task.context_code%TYPE
    , contextkey         IN   task.context_key%TYPE
    , contextcategory    IN   task.context_category%TYPE
    , contexttext        IN   task.context_text%TYPE
    , contextdate        IN   task.context_date%TYPE
    , contextnum         IN   task.context_num%TYPE
    , createuser         IN   task.create_user%TYPE
    , updateuser         IN   task.update_user%TYPE
    , tasksecurity       IN   task.task_security%TYPE
    ) RETURN NUMBER;

   FUNCTION task_update(
      taskseqnum         IN   task.task_seq_num%TYPE
    , taskdate           IN   task.task_date%TYPE
    , tasktext           IN   task.task_text%TYPE
    , taskuser           IN   task.task_user%TYPE
    , taskprioritycode   IN   task.task_priority_code%TYPE
    , taskcategorycode   IN   task.task_category_code%TYPE
    , taskactioncode     IN   task.task_action_code%TYPE
    , notepadflag        IN   task.notepad_flag%TYPE
    , deleteflag         IN   task.delete_flag%TYPE
    , claimid            IN   task.claim_id%TYPE
    , policynum          IN   task.policy_num%TYPE
    , imageseqnum        IN   task.image_seq_num%TYPE
    , contextcode        IN   task.context_code%TYPE
    , contextkey         IN   task.context_key%TYPE
    , contextcategory    IN   task.context_category%TYPE
    , contexttext        IN   task.context_text%TYPE
    , contextdate        IN   task.context_date%TYPE
    , contextnum         IN   task.context_num%TYPE
    , updateuser         IN   task.update_user%TYPE
    , updateseqnum       IN   task.update_seq_num%TYPE
    , tasksecurity       IN   task.task_security%TYPE
    ) RETURN NUMBER;

   FUNCTION task_delete(
      taskseqnum     IN   task.task_seq_num%TYPE
    , updateseqnum   IN   task.update_seq_num%TYPE
    , updateuser     IN   task.update_user%TYPE
    ) RETURN NUMBER;

   PROCEDURE selecttasks(
      taskselectsql   IN       VARCHAR2
    , recordset       IN OUT   ref_cursor);

   PROCEDURE task_purge(
      intasksourcecode     IN       task.task_source_code%TYPE
    , intaskcategorycode   IN       task.task_category_code%TYPE
    , inkeepdays           IN       VARCHAR2
    , outdelrows           OUT      VARCHAR2);

 -- REMOVE THESE AFTER ALL APPS are using the version of saifTasks created in August 2007.
 -- (deprecated PROCEDURE overloads of task_insert / task_update / task_delete follow)
```

</details>

<details>
<summary>PL/SQL package body</summary>

```sql
-- task_select

PROCEDURE task_select (
   taskseqnum   IN       task.task_seq_num%TYPE,
   recordset    IN OUT   ref_cursor
)
IS
BEGIN
   OPEN recordset FOR
      SELECT task.claim_id AS claimid,
             task.context_category AS contextcategory,
             task.context_code AS contextcode,
             task.context_date AS contextdate,
             task.context_key AS contextkey,
             task.context_num AS contextnum,
             task.context_text AS contexttext,
             task.create_date AS createdate,
             task.create_user AS createuser,
             task.delete_flag AS deleteflag,
             task.image_seq_num AS imageseqnum,
             task.notepad_flag AS notepadflag,
             task.policy_num AS policynum,
             task.task_action_code AS taskactioncode,
             task.task_category_code AS taskcategorycode,
             task.task_date AS taskdate,
             task.task_priority_code AS taskprioritycode,
             task.task_security AS tasksecurity,
             task.task_seq_num AS taskseqnum,
             task.task_source_code AS tasksourcecode,
             task.task_text AS tasktext, task.task_user AS taskuser,
             task.update_date AS updatedate,
             task.update_seq_num AS updateseqnum,
             task.update_user AS updateuser
        FROM task
       WHERE task.task_seq_num = taskseqnum;
END task_select;

-- task_insert (function overload, DML only)

   -- Modified to force 0 claimid and policynum to null.
   -- A 0(zero) claimid or policynum does not make sense and is preventing
   -- RI on the table.

   INSERT INTO task
               (claim_id, context_category, context_code, context_date,
                context_key, context_num, context_text, create_date,
                create_user, delete_flag, image_seq_num, notepad_flag,
                policy_num, task_action_code, task_category_code,
                task_date, task_priority_code, task_security,
                task_seq_num, task_source_code, task_text, task_user,
                update_date, update_seq_num, update_user
               )
        VALUES (decode(claimid, 0, null,claimid), contextcategory, contextcode, contextdate,
                contextkey, contextnum, contexttext, SYSDATE,
                createuser, 'N', imageseqnum, notepadflag,
                decode(policynum, 0, null,policynum), taskactioncode, taskcategorycode,
                taskdate, taskprioritycode, tasksecurity,
                task_seq.NEXTVAL, tasksourcecode, tasktext, taskuser,
                SYSDATE, 0, updateuser
               );

   RETURN SQL%ROWCOUNT;

-- task_update (function overload, DML only)

   UPDATE task
      SET claim_id = decode(claimid, 0, null,claimid),
          context_category = contextcategory,
          context_code = contextcode,
          context_date = contextdate,
          context_key = contextkey,
          context_num = contextnum,
          context_text = contexttext,
          delete_flag = deleteflag,
          image_seq_num = imageseqnum,
          notepad_flag = notepadflag,
          policy_num = decode(policynum, 0, null,policynum),
          task_action_code = taskactioncode,
          task_category_code = taskcategorycode,
          task_date = taskdate,
          task_priority_code = taskprioritycode,
          task_security = tasksecurity,
          task_text = tasktext,
          task_user = taskuser,
          update_date = SYSDATE,
          update_seq_num = updateseqnum + 1,
          update_user = updateuser
    WHERE task_seq_num = taskseqnum AND update_seq_num = updateseqnum;

   RETURN SQL%ROWCOUNT;

-- task_delete (function overload)

FUNCTION task_delete (
   taskseqnum     IN   task.task_seq_num%TYPE,
   updateseqnum   IN   task.update_seq_num%TYPE,
   updateuser     IN   task.update_user%TYPE
)
   RETURN NUMBER
IS
BEGIN
   UPDATE task
      SET delete_flag = 'Y',
          update_date = SYSDATE,
          update_seq_num = updateseqnum + 1,
          update_user = updateuser
    WHERE task_seq_num = taskseqnum AND update_seq_num = updateseqnum;

   RETURN SQL%ROWCOUNT;
END task_delete;

-- task_purge

PROCEDURE task_purge (
   intasksourcecode     IN       task.task_source_code%TYPE,
   intaskcategorycode   IN       task.task_category_code%TYPE,
   inkeepdays           IN       VARCHAR2,
   outdelrows           OUT      VARCHAR2
)
AS
BEGIN
   DELETE      task
         WHERE task_source_code = intasksourcecode
           AND task_category_code = intaskcategorycode
           AND delete_flag = 'Y'
           AND TRUNC (update_date) <
                                   TRUNC (SYSDATE - TO_NUMBER (inkeepdays));

   outdelrows := TO_CHAR (SQL%ROWCOUNT);
END;
```

</details>

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts.

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle package specification and body: `WCIS_DBA.pk82036_task` (retrieved via `legends oracle query --name pk82036_task` on 2026-08-31)
- `SAIF.Business.Tasks/clsTask.vb:690–716` — `BeforeDelete` / `AfterDelete` → `pk82036_task.task_delete`
- `SAIF.Business.Tasks/clsTask.vb:718–765` — `BeforeInsert` / `AfterInsert` → `pk82036_task.task_insert`
- `SAIF.Business.Tasks/clsTask.vb:768–790` — `BeforeRetrieve` / `AfterRetrieve` → `pk82036_task.task_select`
- `SAIF.Business.Tasks/clsTask.vb:793–830` — `BeforeUpdate` → `pk82036_task.task_update`
- `SAIF.Business.Tasks/clsTask.vb:1276–1295` — `CriteriaBase` `DeleteSQL` / `InsertSQL` / `RetrieveSQL` / `UpdateSQL` implementing `IBusinessCriteria`
- `SAIF.Business.Tasks/clsTaskCollection.vb:246–275` — `Public Shared Function PurgeTasks` → `PK82036_Task.Task_Purge`
- `SAIF.Application.Claim.Legal/SAIF.Application.Claim.Legal.vbproj:205`, `SAIF.Application.Claim/SAIF.Application.Claim.vbproj:188`, `SAIF.Business.Tasks.Claim/SAIF.Business.Tasks.Claim.vbproj:183` — project references establishing downstream consumption of `SAIF.Business.Tasks`